package com.neusoft.news.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.neusoft.news.bean.News;
import com.neusoft.news.utils.JDBCUtils;

/**
 * 	新闻Dao
 *	1.查询全部新闻
 *	2.根据新闻id查询
 *	3.删除新闻
 *	4.增加新闻
 *	5.修改新闻审核状态（审核新闻）
 *	6.修改新闻
 * @author 刘迪
 *
 */
public class NewsDao {
	/**
	 * 查询全部新闻
	 * @return	新闻List
	 */
	public List<News> selectAllNews(){
		List<News> newslist = new ArrayList<News>();
		try {
			String sql = "select * from \"d_news\"";
			PreparedStatement pst = JDBCUtils.getConnection().prepareStatement(sql);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				News news = new News(
						rs.getString(1),rs.getString(2),
						rs.getString(3),rs.getString(4),
						rs.getString(5),rs.getString(6),
						rs.getString(7),rs.getString(8),
						rs.getString(9),rs.getString(10),
						rs.getString(11)
						);
				newslist.add(news);
			}
			rs.close();
			pst.close();
			JDBCUtils.destroy();
		} catch (SQLException | ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return newslist;
	}
	
	/**
	 * 根据新闻id查询
	 * @param news_id	新闻id
	 * @return	新闻对象
	 */
	public News selectNewsById(String news_id){
		News news = null;
		try {
			String sql = "select * from \"d_news\" where \"news_id\" = ?";
			
			PreparedStatement pst = JDBCUtils.getConnection().prepareStatement(sql);
			pst.setString(1, news_id);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				news = new News(
						rs.getString(1),rs.getString(2),
						rs.getString(3),rs.getString(4),
						rs.getString(5),rs.getString(6),
						rs.getString(7),rs.getString(8),
						rs.getString(9),rs.getString(10),
						rs.getString(11)
						);
			}
			rs.close();
			pst.close();
			JDBCUtils.destroy();
		} catch (SQLException | ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return news;
	}
	
	/**
	 * 根据新闻栏目查询
	 * @param columns_id	栏目id
	 * @return	新闻对象 
	 */
	public List<News> selectNewsByColumn(String columns_id){
		List<News> newslist = new ArrayList<News>();
		try {
			String sql = "select * from \"d_news\" where \"columns_id\" = ?";
			PreparedStatement pst = JDBCUtils.getConnection().prepareStatement(sql);
			pst.setString(1, columns_id);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				News news = new News(
						rs.getString(1),rs.getString(2),
						rs.getString(3),rs.getString(4),
						rs.getString(5),rs.getString(6),
						rs.getString(7),rs.getString(8),
						rs.getString(9),rs.getString(10),
						rs.getString(11)
						);
				newslist.add(news);
			}
			rs.close();
			pst.close();
			JDBCUtils.destroy();
		} catch (SQLException | ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return newslist;
	}
	
	/**
	 * 删除新闻
	 * @param news_id	新闻id
	 * @return	删除个数
	 */
	public int deleteNews(String news_id) {
		int i = 0;
		try {
			String sql = "delete from \"d_news\" where \"news_id\" = ?";
			PreparedStatement pst = JDBCUtils.getConnection().prepareStatement(sql);
			pst.setString(1, news_id);
			i = pst.executeUpdate();
			pst.close();
			JDBCUtils.destroy();
		} catch (SQLException | ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}
	
	/**
	 * 增加新闻
	 * @param news	新闻对象
	 * @return	增加个数
	 */
	public int insertNews(News news) {
		int i = 0;
		try {
			String sql = "insert into \"d_news\" values(?,?,?,?,?,TO_DATE(?, 'yyyy-MM-dd hh24:mi:ss'),?,?,?,?,?)";
			PreparedStatement pst = JDBCUtils.getConnection().prepareStatement(sql);
			//获取当前时间
			Date day=new Date();    
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String strday = df.format(day);
			//将语句补全
			pst.setString(1, news.getNews_id());
			pst.setString(2, news.getNews_title());
			pst.setString(3, news.getNews_intro());
			pst.setString(4, news.getNews_content());
			pst.setString(5, news.getColumns_id());
			pst.setString(6, strday);
			pst.setString(7, news.getNews_photo());
			pst.setString(8, news.getNews_place());
			pst.setString(9, news.getNews_important());
			pst.setString(10, news.getNews_state());
			pst.setString(11, news.getNews_author());
			i = pst.executeUpdate();
			pst.close();
			JDBCUtils.destroy();
			
		} catch (SQLException | ClassNotFoundException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		return i;
	}
	
	/**
	 * 修改新闻审核状态（审核新闻）
	 * @param news	新闻对象
	 * @return	修改个数
	 */
	public int updateNewsState(News news) {
		int i = 0;
		try {
			String sql = "update \"d_news\" set \"news_state\"=? where \"news_id\"=?";
			PreparedStatement pst = JDBCUtils.getConnection().prepareStatement(sql);
			//将语句补全
			pst.setString(1, news.getNews_state());
			pst.setString(2, news.getNews_id());
			i = pst.executeUpdate();
			pst.close();
			JDBCUtils.destroy();
			
		} catch (SQLException | ClassNotFoundException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		return i;
	}
	
	/**
	 * 修改新闻
	 * @param news	新闻对象
	 * @return	修改个数
	 */
	public int updateNews(News news) {
		int i = 0;
		try {
			String sql = "update \"d_news\" set \"news_title\"=?,\"news_intro\"=?,\"news_content\"=?,\"columns_id\"=?,\"news_date\"=?,\"news_photo\"=?,\"news_place\"=?,\"news_important\"=?,\"news_state\"=?,\"news_author\"=? where \"news_id\"=?";
			PreparedStatement pst = JDBCUtils.getConnection().prepareStatement(sql);
			//获取当前时间
			Date day=new Date();
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String strday = df.format(day);
			//将语句补全
			pst.setString(1, news.getNews_title());
			pst.setString(2, news.getNews_intro());
			pst.setString(3, news.getNews_content());
			pst.setString(4, news.getColumns_id());
			pst.setString(5, strday);
			pst.setString(6, news.getNews_photo());
			pst.setString(7, news.getNews_place());
			pst.setString(8, news.getNews_important());
			pst.setString(9, news.getNews_state());
			pst.setString(10, news.getNews_author());
			pst.setString(11, news.getNews_id());
			i = pst.executeUpdate();
			pst.close();
			JDBCUtils.destroy();
			
		} catch (SQLException | ClassNotFoundException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		return i;
	}
	
	
	
	
}
